﻿/**************************
 * Author: Hientt
 * Date: 17/03/2013
 * Module: 
 * Project: OFB
 **************************/
using System;
using System.Linq;
using System.Data.SqlClient;
using System.Data;

namespace Advertising.DataAccess.Common
{
    /// <summary>
    /// This class is perform all connection with database
    /// </summary>
    public class DataObject
    {
        private string connectionString=string.Empty;
        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
            set
            {
                connectionString=value;
            }
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connectionString"></param>
        public DataObject(string connectionString)
        {
            this.connectionString=connectionString;
        }

        /// <summary>
        /// Get Sql Connection
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConnection()
        {
            return new SqlConnection(this.connectionString);
        }

        /// <summary>
        /// Execute a Create, Update, Delete store procedure
        /// </summary>
        /// <param name="storeName"></param>
        /// <param name="paras"></param>
        public void Execute(string storeName, params IDataParameter[] paras)
        {
            using (SqlConnection conn=this.GetConnection())
            {
                conn.Open();

                SqlCommand cmd=new SqlCommand(storeName, conn);
                cmd.CommandType=CommandType.StoredProcedure;
                if (paras != null)
                    cmd.Parameters.AddRange(paras);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Execute a store procedure with the return data
        /// </summary>
        /// <param name="storeName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteQuery(string storeName, params IDataParameter[] paras)
        {
            SqlConnection conn=this.GetConnection();
            conn.Open();
            SqlCommand cmd=new SqlCommand(storeName, conn);
            cmd.CommandType=CommandType.StoredProcedure;
            if (paras != null)
                cmd.Parameters.AddRange(paras);

            SqlDataReader reader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

        /// <summary>
        /// Execute a store procedure with the return data using delegate
        /// </summary>
        /// <param name="storeName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public void Execute(Action<SqlDataReader> handler, string storeName, params IDataParameter[] paras)
        {
            using (SqlConnection conn=this.GetConnection())
            {
                conn.Open();
                SqlCommand cmd=new SqlCommand(storeName, conn);
                cmd.CommandType=CommandType.StoredProcedure;
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                SqlDataReader reader=cmd.ExecuteReader();
                handler(reader);
            }
        }

        /// <summary>
        /// Build a parameter
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public IDataParameter BuildParameter(string paramName, DbType dbType, object value)
        {
            SqlParameter param=new SqlParameter(paramName, dbType);
            param.Value=value;

            return param;
        }

        /// <summary>
        /// Build parameter with ParameterDirection
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="paramDirection"></param>
        /// <returns></returns>
        public IDataParameter BuildParameter(string paramName, SqlDbType dbType, ParameterDirection paramDirection)
        {
            SqlParameter param=new SqlParameter(paramName, dbType);
            param.Direction=paramDirection;

            return param;
        }

        /// <summary>
        /// Build parameter with ParameterDirection
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="paramDirection"></param>
        /// <returns></returns>
        public IDataParameter BuildParameter(string paramName, DbType dbType, ParameterDirection paramDirection)
        {
            SqlParameter param=new SqlParameter(paramName, dbType);
            param.Direction=paramDirection;

            return param;
        }
    }
}
